package com.weixing.mall.provider.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.weixing.mall.base.constant.GlobalConstant;
import com.weixing.mall.core.enums.OrderSysTagEnum;
import com.weixing.mall.provider.mapper.DayReportDeliveryMapper;
import com.weixing.mall.provider.model.domain.Order;
import com.weixing.mall.provider.model.enums.NoSuppllyAuditEnum;
import com.weixing.mall.provider.model.enums.OrderStateEnum;
import com.weixing.mall.provider.model.enums.ProductStatusEnum;
import com.weixing.mall.provider.model.vo.report.delivery.*;
import com.weixing.mall.provider.service.IDayReportDeliveryService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.List;


/**
 * @author jzy
 * @date 2020/6/2
 */
@Service
public class DayReportDeliveryServiceImpl extends ServiceImpl<DayReportDeliveryMapper, Order> implements IDayReportDeliveryService {
    @Override
    public IPage<DeliveryShipperSendDataVo> shipperSendData(Long shipperId, String startTime, String endTime,String monthTime, Integer orderType, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m-%d') date,o.customer_id customerId,o.customer_name customerName" +
                ",count(0) dayOrderQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +
                  "'and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS4.getValue()+
                  "',true,null)) sendOutQuantity" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) cancelQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS3.getValue()+"',true,null)) sendRepertory" +
                ",count(if(o.item_status="+ ProductStatusEnum.STATUS5.getValue() +" and o.ship_time IS NOT null,true,null)) sendExchange" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS3.getValue()+"',true,null)) sendErp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) erpRemain" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) repertoryRemain" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) remainTotal" +
                ",count(if(o.order_price<300,true,null)) hasNoSend" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) refundTotal" +
                ",sum(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.actual_price,0)) refundAmount" +
                ",sum(goods.outer_price) purchasePrice" +
                ",sum(o.shipper_commission) commission" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(monthTime),"date_format(o.audit_time,'%Y-%m')",monthTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("date_format(o.audit_time,'%Y-%m-%d')","o.customer_id");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间倒序
        q.orderByDesc(StrUtil.isEmpty(sortField), "o.audit_time");
        //return baseMapper.shipperSendData(page, q);
        return baseMapper.shipperSendData2(page,startTime,endTime,monthTime,shipperId,orderType);
    }

    @Override
    public DeliveryShipperSendDataVo shipperSendDataTotal(Long shipperId, String startTime, String endTime,String monthTime, Integer orderType, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m-%d') date,o.customer_id customerId,o.customer_name customerName" +
                ",count(0) dayOrderQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"' and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS4.getValue()+"',true,null)) sendOutQuantity" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) cancelQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS3.getValue()+"',true,null)) sendRepertory" +
                ",count(if(o.item_status="+ ProductStatusEnum.STATUS5.getValue() +" and o.ship_time IS NOT null ,true,null)) sendExchange" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and o.ship_time IS NOT null and o.item_status='"+ProductStatusEnum.STATUS3.getValue()+"',true,null)) sendErp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) erpRemain" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) repertoryRemain" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) remainTotal" +
                ",count(if(o.order_price<300,true,null)) hasNoSend" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) refundTotal" +
                ",sum(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.actual_price,0)) refundAmount" +
                ",sum(goods.cost_price) purchasePrice" +
                ",sum(o.shipper_commission) commission" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(monthTime),"date_format(o.audit_time,'%Y-%m')",monthTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //return baseMapper.shipperSendDataTotal(page, q);
        return baseMapper.shipperSendDataTotal2(page, startTime,endTime,monthTime,shipperId,orderType);
    }

    @Override
    public IPage<DeliveryShipperSendDataDetailVo> shipperSendDataDetail(Integer type,Long customerId,Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,o.audit_time,o.ship_time operateTime,o.order_price orderPrice");
        q.eq(customerId!=null,"o.customer_id",customerId);
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());        }
        if (type!=null&&type==2){
            //q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
            q.eq("o.item_status",ProductStatusEnum.STATUS4.getValue());
            q.isNotNull("o.ship_time");
        }
        if (type!=null&&type==3){
            q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
        }
        if (type!=null&&type==4){
           // q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
            q.eq("o.item_status",ProductStatusEnum.STATUS3.getValue());
            q.isNotNull("o.ship_time");
        }
        if (type!=null&&type==5){
            //q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
            q.eq("o.item_status",ProductStatusEnum.STATUS5.getValue());
            q.isNotNull("o.ship_time");
        }
        if (type!=null&&type==6){
            q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
            q.eq("o.item_status",ProductStatusEnum.STATUS3.getValue());
            q.isNotNull("o.ship_time");
        }
        if (type!=null&&type==7){
            q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        if (type!=null&&type==8){
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        if (type!=null&&type==9){
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        if (type!=null&&type==10){
            q.lt("o.order_price",300);
        }
        if(type!=null&&(type==1 || type==7 || type==10)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        }else if(type!=null&&type==3 ) {
            q.ge(StrUtil.isNotEmpty(startTime), "date_format(o.nosupplly_time,'%Y-%m-%d')", startTime);
            q.le(StrUtil.isNotEmpty(endTime), "date_format(o.nosupplly_time,'%Y-%m-%d')", endTime);
            q.eq("o.nosupplly_audit",NoSuppllyAuditEnum.TYPE_1.getValue());
        }else if(type!=null&&(type==2 ||type==4 || type==5 || type==6)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }else {
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("o.id");
        q.orderByDesc( "o.audit_time");
        //return baseMapper.shipperSendDataDetail(page, q);
        return baseMapper.shipperSendDataDetail2(page, q);
    }

    @Override
    public IPage<DeliveryShipperSendDataStatisticsVo> shipperSendDataStatistics(Long shipperId, String yearMonth, Integer orderType, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        DateTime parse = DateUtil.parse(yearMonth, "yyyy-MM");
        DateTime lastMonth = DateUtil.offsetMonth(parse, -1);
        String lastMonthStr = DateUtil.format(lastMonth, "yyyy-MM");
        //上月/以前月的单（这个月也没发）
        String condition="oo.shipper_id=o.shipper_id and date_format(oo.audit_time,'%Y-%m')<='"+lastMonthStr+"' and oo.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue();
        //上月/以前月的单（这个月发的）
        String condition1="oo.shipper_id=o.shipper_id and date_format(oo.audit_time,'%Y-%m')<='"+lastMonthStr+"' and date_format(oo.ship_time,'%Y-%m')='"+yearMonth+"'";
        //上月/以前的单（这月断的货）
        String condition2="oo.shipper_id=o.shipper_id and date_format(oo.audit_time,'%Y-%m')<='"+lastMonthStr+"' and date_format(oo.nosupplly_time,'%Y-%m')='"+yearMonth+"'";
        q.select("o.shipper_id shipperId,o.shipper_name shipperName" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"',true,null)) currentMonthReceive" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.depot_code='"+ GlobalConstant.DepotCode.APP +"',true,null)) currentMonthApp" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) currentMonthStockout" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.depot_code='"+ GlobalConstant.DepotCode.OUT +"',true,null)) currentMontSendSelf" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.depot_code='"+ GlobalConstant.DepotCode.APP +"',true,null)) currentMontSendApp" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.depot_code='"+ GlobalConstant.DepotCode.ERP +"',true,null)) currentMontSendErp" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.sys_tag="+ OrderSysTagEnum.ZHH_TAG_305.getValue() +",true,null)) currentMontSendExchange" +
                ",count(if(date_format(o.audit_time,'%Y-%m')='"+yearMonth+"' and o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) currentMonthNoSend" +
                ",(select count(0) from omc_order oo where ("+condition+") or("+condition1+")) lastMonthOrders" +
                ",(select count(0) from omc_order oo where "+condition2+") lastMonthStockout" +
                ",(select count(0) from omc_order oo where "+condition1+" and oo.depot_code='"+ GlobalConstant.DepotCode.OUT +"') lastMontSendSelf" +
                ",(select count(0) from omc_order oo where "+condition1+" and oo.depot_code='"+ GlobalConstant.DepotCode.APP +"') lastMontSendApp" +
                ",(select count(0) from omc_order oo where "+condition1+" and oo.depot_code='"+ GlobalConstant.DepotCode.ERP +"') lastMontSendErp" +
                ",(select count(0) from omc_order oo left join omc_after_sale afterSale1 on afterSale1.order_id = oo.id where "+condition1+" and o.sys_tag="+ OrderSysTagEnum.ZHH_TAG_305.getValue() +") lastMontSendExchange" +
                ",(select count(0) from omc_order oo where "+condition+") lastMonthNoSend" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        ObjectUtil.isEmpty(yearMonth);
        //本月的订单和以前月份残留
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth).or().eq("date_format(o.ship_time,'%Y-%m')",yearMonth).or().eq("date_format(o.nosupplly_time,'%Y-%m')",yearMonth).or().and(j->j.le("date_format(o.audit_time,'%Y-%m')",lastMonth).eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue())));
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.isNotNull("o.shipper_id");
        q.groupBy("o.shipper_id");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc(StrUtil.isEmpty(sortField), "o.audit_time");
        IPage mapIPage = baseMapper.shipperSendDataStatistics(page, q);
        List<DeliveryShipperSendDataStatisticsVo> records = mapIPage.getRecords();
        records.forEach(vo->{
            //接单小计
            vo.setSubtotal(vo.getCurrentMonthReceive()+vo.getCurrentMonthApp()+vo.getLastMonthOrders());
            // 断货小计
            vo.setStockoutSubtotal(vo.getCurrentMonthStockout()+vo.getLastMonthStockout());
            // 发货小计
            vo.setSendSubtotal(vo.getCurrentMontSendSelf()+vo.getCurrentMontSendApp()+vo.getCurrentMontSendErp()+vo.getCurrentMontSendExchange()+vo.getLastMontSendSelf()+vo.getLastMontSendApp()+vo.getLastMontSendErp()+vo.getLastMontSendExchange());
            //留存小计
            vo.setNoSendSubtotal(vo.getCurrentMonthNoSend()+vo.getLastMonthNoSend());
        });
        return mapIPage;
    }

    @Override
    public IPage<DeliveryShipperSendDataDetailVo> shipperSendDataStatisticsDetail(Integer type, String yearMonth,Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,date_format(o.audit_time,'%Y-%m-%d') auditTime,date_format(o.ship_time,'%Y-%m-%d') operateTime,o.order_price orderPrice");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        DateTime parse = DateUtil.parse(yearMonth, "yyyy-MM");
        DateTime lastMonthDate = DateUtil.offsetMonth(parse, -1);
        String lastMonth = DateUtil.format(lastMonthDate, "yyyy-MM");
        //本月接单
        if (type!=null&&type==1){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        }
        //本月转单  转单指匹配到APP仓的订单
        if (type!=null&&type==2){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
        }
        //上月留存订单  (上月未发和本月已发上月的)
        if (type!=null&&type==3){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue()).or().eq("date_format(o.ship_time,'%Y-%m')",yearMonth));
        }
        //接单小计
        if (type!=null&&type==4){
            q.le(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue()).or().eq("date_format(o.ship_time,'%Y-%m')",yearMonth));
        }
        //断货本月订单
        if (type!=null&&type==5){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.item_status",OrderSysTagEnum.QXK_TAG_401.getValue()));
        }
        //6断货上月订单
        if (type!=null&&type==6){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.nosupplly_time,'%Y-%m')",yearMonth);
        }
        //7断货小计
        if (type!=null&&type==7){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.nosupplly_time,'%Y-%m')",yearMonth);
        }
        //8上月自采自发
        if (type!=null&&type==8){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
        }
        //9上月发APP仓
        if (type!=null&&type==9){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
        }
        //10上月发ERP仓
        if (type!=null&&type==10){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
        }
        //11上月发换码
        if (type!=null&&type==11){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.ship_time,'%Y-%m')",yearMonth);
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        //12本月自采自发
        if (type!=null&&type==12){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
        }
        //13本月发APP仓
        if (type!=null&&type==13){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
        }
        //14本月发ERP仓
        if (type!=null&&type==14){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
        }
        //15本月发换码
        if (type!=null&&type==15){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());        }
        //16发货小计
        if (type!=null&&type==16){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.ship_time,'%Y-%m')",yearMonth);
        }
        //17留存上月订单
        if (type!=null&&type==17){
            q.le(StrUtil.isNotEmpty(lastMonth),"date_format(o.audit_time,'%Y-%m')",lastMonth);
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        //18留存本月订单
        if (type!=null&&type==18){
            q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        //19留存小计
        if (type!=null&&type==19){
            q.le(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc(StrUtil.isEmpty(sortField), "o.audit_time");
        IPage mapIPage = baseMapper.shipperSendDataStatisticsDetail(page, q);
        return mapIPage;
    }

    @Override
    public List<DeliveryShipperExchangeReportVo> shipperExchangeReport(Long shipperId, String yearMonth, Integer orderType, String sortField, String sortType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m-%d') date,count(0) totalQuantity" +
                /*",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"',true,null)) sendOut" +
                ",IFNULL(sum(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"',goods.outer_price,0)),0) outPrice" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) stockout" +*/
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"',true,null)) sendApp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"',true,null)) sendErp" +
                ",count(if(o.order_status="+ OrderStateEnum.WAIT_SHIP.getValue() +",true,null)) remain" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("date_format(o.audit_time,'%Y-%m-%d')");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc("o.audit_time");
        List listMaps = baseMapper.shipperExchangeReport(q);
        //计算 上月、上上月留存
        List lasts = packageExchangeReport(shipperId, yearMonth, orderType);
        lasts.addAll(listMaps);
        return lasts;
    }
    /**
     * 计算上月、上上月的留存
     * @param shipperId
     * @param yearMonth
     * @param orderType
     * @return
     */
    public List<DeliveryShipperExchangeReportVo> packageExchangeReport(Long shipperId, String yearMonth, Integer orderType){
        DateTime parse = DateUtil.parse(yearMonth, "yyyy-MM");
        DateTime lastMonthDate = DateUtil.offsetMonth(parse, -1);
        DateTime lastTwoMonthDate = DateUtil.offsetMonth(parse, -2);
        String lastMonth = DateUtil.format(lastMonthDate, "yyyy-MM");
        String lastTwoMonth = DateUtil.format(lastTwoMonthDate, "yyyy-MM");
        //筛选上月、上上月的留存
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m') date" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +" or date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) totalQuantity" +
                /*",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendOut" +
                ",IFNULL(sum(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',goods.outer_price,0)),0) outPrice" +
                ",count(if((o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +") and date_format(o.nosupplly_time,'%Y-%m')='"+yearMonth+"',true,null)) stockout" +*/
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendApp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendErp" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) remain" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        q.and(i->i.eq("date_format(o.audit_time,'%Y-%m')",lastMonth).or().eq("date_format(o.audit_time,'%Y-%m')",lastTwoMonth));
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("date_format(o.audit_time,'%Y-%m')");
        //默认时间正序
        q.orderByAsc("o.audit_time");
        List listMaps = baseMapper.shipperExchangeReport(q);
        return listMaps;
    }
    @Override
    public IPage<DeliveryShipperSendDataDetailVo> shipperExchangeReportDetail(Integer type, String date, String yearMonth, Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,date_format(o.audit_time,'%Y-%m-%d') auditTime,date_format(o.ship_time,'%Y-%m-%d') operateTime,o.order_price orderPrice");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        //只查换货订单
        q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        //1总单
        if (type!=null&&type==1){
            //大于7是年月日 发出和未发出所有
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            }else{//年月 留存（上月/上上月留存未发出 和这个月发的上月/上上月的）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue()).or().eq("date_format(o.ship_time,'%Y-%m')",yearMonth));
            }

        }
        //2采购
        if (type!=null&&type==2){
            //大于7是年月日 发外采
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
            }else{//年月 外采（这个月发的上月/上上月的外采）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //3断货
        if (type!=null&&type==3){
            //大于7是年月日 断货
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
            }else{//年月 断货（这个月断货上月/上上月的）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("date_format(o.nosupplly_time,'%Y-%m')",yearMonth);
            }
        }
        //4库存
        if (type!=null&&type==4){
            //大于7是年月日 app
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
            }else{//年月 app（这个月发上月/上上月的app）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //5ERP
        if (type!=null&&type==5){
            //大于7是年月日 erp
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
            }else{//年月 erp（这个月发上月/erp）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //6留存
        if (type!=null&&type==6){
            //大于7是年月日 未发出
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
            }else{//年月 未发出（这个月发上月 未发出）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
            }
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc( "o.audit_time");
        return baseMapper.shipperPerformanceDetail(page, q);
    }

    @Override
    public IPage<DeliveryShipperOutVo> shipperOutReport(Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,o.audit_time,o.ship_time operateTime,o.order_price orderPrice,goods.outer_price outPrice");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc("o.audit_time");
        return baseMapper.shipperOutReport(page, q);
    }

    @Override
    public IPage<DeliveryShipperPerformanceVo> shipperPerformance(Long shipperId,Long actId,Integer orderType, String startTime, String endTime, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String sendCondition="o.ship_time is not null";
        String stockoutCondition= "o.nosupplly_audit = "+NoSuppllyAuditEnum.TYPE_1.getValue()+" AND o.item_status = "+ProductStatusEnum.STATUS6.getValue()+" " ;//"o.nosupplly_time is not null";
        String cancelCondition= "o.nosupplly_audit = "+NoSuppllyAuditEnum.TYPE_1.getValue()+" AND o.item_status = "+ProductStatusEnum.STATUS7.getValue()+" " ;//"o.canceled_time is not null";
        String returnCondition="afterSale.audited_time is not null";
        if (StrUtil.isNotEmpty(startTime)){
            sendCondition=sendCondition+" and o.ship_time>='"+startTime+"'";
            stockoutCondition=stockoutCondition+" and o.nosupplly_time>='"+startTime+"'";
            //cancelCondition=cancelCondition+" and o.canceled_time>='"+startTime+"'";
            cancelCondition=cancelCondition+" and o.nosupplly_time>='"+startTime+"'";
            returnCondition=returnCondition+" and afterSale.audited_time>='"+startTime+"'";
        }
        if (StrUtil.isNotEmpty(endTime)){
            sendCondition=sendCondition+" and o.ship_time<='"+endTime+"'";
            stockoutCondition=stockoutCondition+" and o.nosupplly_time<='"+endTime+"'";
           // cancelCondition=cancelCondition+" and o.canceled_time<='"+endTime+"'";
            cancelCondition=cancelCondition+" and o.nosupplly_time<='"+endTime+"'";
            returnCondition=returnCondition+" and afterSale.audited_time<='"+endTime+"'";
        }
        q.select("sysUser.id shipperId,sysUser.real_name shipperName,sysUser.user_star starLevel" +
                ",count(if("+sendCondition+",true,null)) sendQuantity" +
                ",count(if("+stockoutCondition+",true,null)) stockoutQuantity" +
                ",count(if("+cancelCondition+",true,null)) cancelQuantity" +
                ",count(if("+returnCondition+",true,null)) returnQuantity" +
                ",(count(if("+sendCondition+",true,null))-count(if("+returnCondition+",true,null))) actualSendQuantity" +
                "");
        q.eq(shipperId!=null,"sysUser.id",shipperId);
        q.eq(actId!=null,"o.act_id",actId);
        q.eq(orderType!=null,"o.order_type",orderType);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.groupBy("sysUser.id");
        return baseMapper.shipperPerformanceMaps(page, q);
    }

    @Override
    public IPage<DeliveryShipperSendDataDetailVo> shipperPerformanceDetail(Integer type,Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,date_format(o.audit_time,'%Y-%m-%d') auditTime,date_format(o.ship_time,'%Y-%m-%d') operateTime,o.order_price orderPrice");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());        }
        //1发货数
        if (type!=null&&type==1){
            q.isNotNull("o.ship_time");
        }
        //2断货数
        if (type!=null&&type==2){
            //q.isNotNull("o.nosupplly_time");
            q.eq("o.nosupplly_audit",NoSuppllyAuditEnum.TYPE_1.getValue());
            q.eq("o.item_status",ProductStatusEnum.STATUS6.getValue());
        }
        //3取消数
        if (type!=null&&type==3){
            //q.isNotNull("o.canceled_time");
            q.eq("o.nosupplly_audit",NoSuppllyAuditEnum.TYPE_1.getValue());
            q.eq("o.item_status",ProductStatusEnum.STATUS7.getValue());
        }
        //4退货数
        if (type!=null&&type==4){
            q.isNotNull("afterSale.audited_time");
        }
        //5实际发货数
        if (type!=null&&type==5){
            q.isNotNull("o.ship_time");
            q.isNull("afterSale.audited_time");
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc( "o.audit_time");
        return  baseMapper.shipperSendDataDetail(page, q);
    }

    @Override
    public List<DeliveryShipperRemainReportVo> shipperRemainReport(Long shipperId, String yearMonth, Integer orderType, String sortField,String groupByType, String sortType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m-%d') date" +
                ",date_format(o.audit_time,'%Y-%m') monthDate" +
                ",o.shipper_name shipperName" +
                ",count(0) totalQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"',true,null)) sendOut" +
                ",IFNULL(sum(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"',goods.outer_price,0)),0) outPrice" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) stockout" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"',true,null)) sendApp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"',true,null)) sendErp" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) remain" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        if(ObjectUtil.isNotEmpty(groupByType) && groupByType.equals("month")){
            q.groupBy("shipperName","date_format(o.audit_time,'%Y-%m')");
        }else if(ObjectUtil.isNotEmpty(groupByType) && groupByType.equals("day")){
            q.groupBy("shipperName","date_format(o.audit_time,'%Y-%m-%d')");
        }else{
            q.groupBy("shipperName","date_format(o.audit_time,'%Y-%m-%d')","date_format(o.audit_time,'%Y-%m')");
        }
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc(StrUtil.isEmpty(sortField), "o.audit_time");
        List listMaps = baseMapper.shipperRemainReport(q);
        //计算 上月、上上月留存
        List lasts = packageShipperRemainReport(shipperId, yearMonth, orderType);
        lasts.addAll(listMaps);
        return lasts;
    }
    /**
     * 计算上月、上上月的留存
     * @param shipperId
     * @param yearMonth
     * @param orderType
     * @return
     */
    public List<DeliveryShipperRemainReportVo> packageShipperRemainReport(Long shipperId, String yearMonth, Integer orderType){
        DateTime parse = DateUtil.parse(yearMonth, "yyyy-MM");
        DateTime lastMonthDate = DateUtil.offsetMonth(parse, -1);
        DateTime lastTwoMonthDate = DateUtil.offsetMonth(parse, -2);
        String lastMonth = DateUtil.format(lastMonthDate, "yyyy-MM");
        String lastTwoMonth = DateUtil.format(lastTwoMonthDate, "yyyy-MM");
        //筛选上月、上上月的留存
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m') date" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +" or date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) totalQuantity" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendOut" +
                ",IFNULL(sum(if(o.depot_code='"+ GlobalConstant.DepotCode.OUT +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',goods.outer_price,0)),0) outPrice" +
                ",count(if((o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +") and date_format(o.nosupplly_time,'%Y-%m')='"+yearMonth+"',true,null)) stockout" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendApp" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.ERP +"' and date_format(o.ship_time,'%Y-%m')='"+yearMonth+"',true,null)) sendErp" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) remain" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.and(i->i.eq("date_format(o.audit_time,'%Y-%m')",lastMonth).or().eq("date_format(o.audit_time,'%Y-%m')",lastTwoMonth));
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("date_format(o.audit_time,'%Y-%m')");
        //默认时间正序
        q.orderByAsc("o.audit_time");
        List listMaps = baseMapper.shipperRemainReport(q);
        return listMaps;
    }


    @Override
    public IPage<DeliveryShipperSendDataDetailVo> shipperRemainReportDetail(Integer type, String date, String yearMonth, Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,date_format(o.audit_time,'%Y-%m-%d') auditTime,date_format(o.ship_time,'%Y-%m-%d') operateTime,o.order_price orderPrice");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());        }
        //1总单
        if (type!=null&&type==1){
            //大于7是年月日 发出和未发出所有
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            }else{//年月 留存（上月/上上月留存未发出 和这个月发的上月/上上月的）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue()).or().eq("date_format(o.ship_time,'%Y-%m')",yearMonth));
            }

        }
        //2采购
        if (type!=null&&type==2){
            //大于7是年月日 发外采
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
            }else{//年月 外采（这个月发的上月/上上月的外采）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.OUT);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //3断货
        if (type!=null&&type==3){
            //大于7是年月日 断货
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.and(i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
            }else{//年月 断货（这个月断货上月/上上月的）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("date_format(o.nosupplly_time,'%Y-%m')",yearMonth);
            }
        }
        //4库存
        if (type!=null&&type==4){
            //大于7是年月日 app
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
            }else{//年月 app（这个月发上月/上上月的app）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.APP);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //5ERP
        if (type!=null&&type==5){
            //大于7是年月日 erp
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
            }else{//年月 erp（这个月发上月/erp）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.depot_code",GlobalConstant.DepotCode.ERP);
                q.eq("date_format(o.ship_time,'%Y-%m')",yearMonth);
            }
        }
        //6留存
        if (type!=null&&type==6){
            //大于7是年月日 未发出
            if (date.length()>7){
                q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
                q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
            }else{//年月 未发出（这个月发上月 未发出）
                q.eq("date_format(o.audit_time,'%Y-%m')",date);
                q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
            }
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc(StrUtil.isEmpty(sortField), "o.audit_time");
        return baseMapper.shipperPerformanceDetail(page, q);
    }

    @Override
    public IPage<DeliveryWholeReportVo> wholeReport(Long shipperId,Long actId, String startTime, String endTime,String monthTime, Integer orderType, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("date_format(o.audit_time,'%Y-%m-%d') date,o.customer_id customerId,o.customer_name customerName" +
                ",count(0) quantity" +
                ",count(if(o.ship_time is not null,true,null)) sendQuantity" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",true,null)) stockoutQuantity" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +",true,null)) cancelQuantity" +
                ",count(if(o.sys_tag="+OrderSysTagEnum.YXH_TAG_303.getValue()+",true,null)) hasGoods" +
                ",count(if(o.sys_tag="+OrderSysTagEnum.DD_TAG_301.getValue()+",true,null)) waitConfirm" +
                ",count(if(o.sys_tag="+OrderSysTagEnum.DH_TAG_302.getValue()+",true,null)) waitGoods" +
                ",count(if(o.item_status="+ProductStatusEnum.STATUS5.getValue()+" and o.ship_time is not null,true,null)) exchangeSend" +
                ",count(if(o.item_status="+ProductStatusEnum.STATUS5.getValue()+" and o.sys_tag="+OrderSysTagEnum.DFH_TAG_108.getValue()+",true,null)) exchangeNoSend" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.ship_time is not null,true,null)) appSend" +
                ",count(if(o.depot_code='"+ GlobalConstant.DepotCode.APP +"' and o.sys_tag="+OrderSysTagEnum.DFH_TAG_108.getValue()+",true,null)) appNoSend" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.DFH_TAG_108.getValue() +",true,null)) dfhTotal" +
                ",count(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) refundTotal" +
                ",sum(if(o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.actual_price,0)) refundAmount" +
                ",sum(o.order_price) amount" +
                "");
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(actId!=null,"o.act_id",actId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(monthTime),"date_format(o.audit_time,'%Y-%m')",monthTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.groupBy("date_format(o.audit_time,'%Y-%m-%d')","o.customer_id");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc(StrUtil.isEmpty(sortField), "o.audit_time");
        return baseMapper.wholeReport(page, q);
    }

    @Override
    public IPage<DeliveryShipperSendDataDetailVo> wholeReportDetail(Integer type,String date,Long customerId, Long shipperId, String startTime, String endTime, Integer orderType, Integer orderStatus, Integer itemStatus, String userName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_name goodsName,goods.style_no styleNo,goods.sku_desp skuDesp,goods.color" +
                ",goods.size,o.shipper_id shipperId,o.shipper_name shipperName,o.depot_id depotId,o.depot_name depotName,o.order_status orderStatus" +
                ",o.agent_id agentId,o.agent_name agentName,o.customer_id customerId,o.customer_name customerName,address.contact_user contactUser" +
                ",address.contact_phone contactPhone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.order_type orderType,date_format(o.audit_time,'%Y-%m-%d') auditTime,date_format(o.ship_time,'%Y-%m-%d') operateTime,o.order_price orderPrice");
        q.eq(customerId!=null,"o.customer_id",customerId);
        q.eq(shipperId!=null,"o.shipper_id",shipperId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(itemStatus!=null,"o.item_status",itemStatus);
        if (orderStatus!=null&&orderStatus==1){
            q.ne("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        if (orderStatus!=null&&orderStatus==2){
            q.eq("o.sys_tag",OrderSysTagEnum.ZHH_TAG_305.getValue());
        }
        //2发出
        if (type!=null&&type==2){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.isNotNull("o.ship_time");
        }
        //3断货
        if (type!=null&&type==3){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue());
        }
        //4取消
        if (type!=null&&type==4){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue());
        }
        //5有现货
        if (type!=null&&type==5){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.sys_tag",OrderSysTagEnum.YXH_TAG_303.getValue());
        }
        //6待定
        if (type!=null&&type==6){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.sys_tag",OrderSysTagEnum.DD_TAG_301.getValue());
        }
        //7待货
        if (type!=null&&type==7){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.sys_tag",OrderSysTagEnum.DH_TAG_302.getValue());
        }
        //8置换发出
        if (type!=null&&type==8){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.item_status",ProductStatusEnum.STATUS5.getValue());
            q.isNotNull("o.ship_time");
        }
        //9置换未发
        if (type!=null&&type==9){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.item_status",ProductStatusEnum.STATUS5.getValue());
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        //10APP仓发出
        if (type!=null&&type==10){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP );
            q.isNotNull("o.ship_time");
        }
        //11APP仓未发
        if (type!=null&&type==11){
            q.eq("date_format(o.audit_time,'%Y-%m-%d')",date);
            q.eq("o.depot_code",GlobalConstant.DepotCode.APP );
            q.eq("o.sys_tag",OrderSysTagEnum.DFH_TAG_108.getValue());
        }
        q.like(StrUtil.isNotEmpty(userName),"address.contact_user",userName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.orderByDesc( "o.audit_time");
        return  baseMapper.shipperSendDataDetail(page, q);
    }

}
